package br.com.sigde.dao;

import java.lang.annotation.Annotation;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;

import br.com.sigde.anotacao.OrmAtributo;
import br.com.sigde.anotacao.OrmNucleo;
import br.com.sigde.anotacao.OrmReference;
import br.com.sigde.anotacao.TipoAtributo;
import br.com.sigde.framework.modelo.ConnectionProvider;
import br.com.sigde.framework.util.ObjectUtil;
import br.com.sigde.modelo.Modelo;


public abstract class SigdeDAO {

	public abstract void incluir(Modelo modelo);
	public abstract void alterar(Modelo modelo);
	public abstract void excluir(Modelo modelo);
	public abstract List<Modelo> recuperar();
	public abstract Modelo recuperar(Modelo modelo);
	
	private ResultSet resultSet;
	private Statement statement;
	private Connection connection;
	private ResultSetMetaData metadado;
	protected final String UPDATE="UPDATE";
	protected final String INSERT="INSERT";
	
	SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy HH:mm:ss");
	
	
	/**
	 * Executa um comando de consulta
	 * @param sql
	 * @return
	 * @throws RegraException 
	 */
	protected final boolean executaComando(String sql) {
		try{
			String tpComando = sql.trim().substring(0,6);
			connection = getConnection();
			statement = connection.createStatement();
			if(tpComando.equalsIgnoreCase("SELECT")){
				resultSet = statement.executeQuery(sql);
				metadado = resultSet.getMetaData();
			}else{ 
				statement.executeUpdate(sql);
				connection.commit();
				fechaConexao();
			}
			return true;
		}catch(SQLException sqlException ){
			fechaConexao();
			return false;
		}
	}
	
	
	public Object recuperaRegistro(Object objeto){
		StringBuffer sql1 = new StringBuffer(" SELECT ");
		StringBuffer sql2 = new StringBuffer(" FROM ");
		StringBuffer sql3 = new StringBuffer(" WHERE ");
		Object retorno = null;
		try {
			String nomeColuna= null;
			Field[] atributos = objeto.getClass().getDeclaredFields();
			Annotation annotation = objeto.getClass().getAnnotation(OrmNucleo.class);
			OrmNucleo ormNucleo = (OrmNucleo)annotation;
			sql2.append(ormNucleo.nome_schema());
			sql2.append(".");
			sql2.append(ormNucleo.nome_nucleo());
			Field atributo=null;
			for(int f=0;f<atributos.length;f++){
				atributo = atributos[f];	
				atributo.setAccessible(true);
				Annotation annotationAtributo = atributo.getAnnotation(OrmAtributo.class);
				OrmAtributo orm = (OrmAtributo)annotationAtributo;
				if(ObjectUtil.isEmpty(orm))
					continue;
				sql1.append(orm.nome_atribudo());
				if(f+1 != atributos.length)
					sql1.append(",");
				if(orm.chave()){
					sql3.append(orm.nome_atribudo());
					sql3.append(" = ");
					recuperaValorAtributo(sql3,atributo,orm.tipo_atributo(),objeto);
				}
			}	
			removeUltimoCaracter(sql1, ",");
			String sql = sql1.toString()+sql2.toString()+sql3.toString();
			executaComando(sql);
			proximoRegistro();
			retorno = montaRegistro(objeto.getClass());
		} catch (Exception e) {
			fechaConexao();
			e.printStackTrace();
		}finally{
			fechaConexao();
		}
		return retorno;
		
	}
	
	/**
	 * verifica e posiciona o resultset no proximo registro
	 * @return
	 */
	protected final boolean proximoRegistro(){
		boolean retorno = false;
		try {
			retorno = resultSet.next();
			if(!retorno){
				fechaConexao();
				return false;
			}
			return true;			
		} catch (SQLException e) {
			fechaConexao();
			return false;
		}		
	}
	
	/**
	 * recupea o proximo registro
	 * @param modelo_class
	 * @return
	 */
	protected final Object montaRegistro(Class modelo_class){
		Object retorno= null;
		try {
			int numColumns = metadado.getColumnCount();
			retorno = modelo_class.newInstance();
			String nomeColuna= null;
			Field[] atributos = retorno.getClass().getDeclaredFields();
			Field atributo=null;
			Object atributoDB=null;
			for (int i=0; i<numColumns; i++) {
				nomeColuna = metadado.getColumnName(i+1);
				for(int f=0;f<atributos.length;f++){
					Annotation annotation = atributos[f].getAnnotation(OrmAtributo.class);
					OrmAtributo orm = (OrmAtributo)annotation;
					if(ObjectUtil.isEmpty(orm))
						continue;
					if(orm.nome_atribudo().equalsIgnoreCase(nomeColuna)){
						atributo = atributos[f];
						atributoDB=resultSet.getObject(nomeColuna);
						montaLinhaRetorno(retorno, atributo, atributoDB);
						break;
					}else{
						continue;
					}
				}
			}
		} catch (Exception e) {
			fechaConexao();
			e.printStackTrace();
		}
		return retorno;
	}
	
	/**
	 * monta a linha da retorno 
	 * @param retorno
	 * @param atributo
	 * @param atributoDB
	 * @throws IllegalAccessException
	 * @throws SQLException
	 */
	private void montaLinhaRetorno(Object retorno, Field atributo,Object atributoDB) throws IllegalAccessException, SQLException {
		atributo.setAccessible(true);
		if(!ObjectUtil.isEmpty(atributoDB)){
			if(atributoDB instanceof java.math.BigDecimal){
				java.math.BigDecimal vl = (java.math.BigDecimal)atributoDB;
				if(atributo.getType().getName().equals("java.lang.Long"))
					atributo.set(retorno, new Long(vl.longValue()));
				else if(atributo.getType().getName().equals("java.lang.Double"))
					atributo.set(retorno, new Double(vl.doubleValue()));
				else if(atributo.getType().getName().equals("java.lang.Integer"))
					atributo.set(retorno, new Integer(vl.intValue()));
				else if(atributo.getType().getName().equals("java.lang.String"))
					atributo.set(retorno, String.valueOf(vl.intValue()).trim());
				else if(atributo.getType().getName().equals("java.math.BigDecimal"))
					atributo.set(retorno, vl);
			}
			else if(atributoDB instanceof String){
				String vl = (String)atributoDB;
				if(atributo.getType().getName().equals("char"))
					atributo.set(retorno, vl.trim().charAt(0));
				else if(atributo.getType().getName().equals("java.lang.String"))
					atributo.set(retorno, vl.trim());
				else if(atributo.getType().getName().equals("java.util.Date")){
					try{
						Date data = sdf.parse(vl.trim());
						atributo.set(retorno, data);
					}catch(Exception e){
						e.printStackTrace();
					}
				}	
				if(atributo.getType().getName().equals("java.lang.Long"))
					atributo.set(retorno, new Long(vl.trim()));
	
			}
			else if(atributoDB instanceof Integer){
				Integer vl = (Integer)atributoDB;
				if(atributo.getType().getName().equals("java.lang.Long"))
					atributo.set(retorno, new Long(vl.intValue()));
				else if(atributo.getType().getName().equals("java.lang.Integer"))
					atributo.set(retorno, new Integer(vl.intValue()));
				else if(atributo.getType().getName().equals("java.lang.String"))
					atributo.set(retorno, String.valueOf(vl.intValue()));
				
			}else if(atributoDB instanceof java.sql.Date)
				atributo.set(retorno, new Date(((java.sql.Date)atributoDB).getTime()));
			else if(atributoDB instanceof java.sql.Timestamp)
				atributo.set(retorno, new Date(((java.sql.Timestamp)atributoDB).getTime()));
		}	
	}
	
	protected boolean insert(Object objeto){
		StringBuffer sql1 = new StringBuffer("INSERT INTO ");
		StringBuffer sql2 = new StringBuffer("(");
		StringBuffer sql3 = new StringBuffer(" VALUES(");
		boolean retorno= false;
		try {
			String nomeColuna= null;
			Field[] atributos = objeto.getClass().getDeclaredFields();
			Annotation annotation = objeto.getClass().getAnnotation(OrmNucleo.class);
			OrmNucleo ormNucleo = (OrmNucleo)annotation;
			sql1.append(ormNucleo.nome_schema());
			sql1.append(".");
			sql1.append(ormNucleo.nome_nucleo());
			Field atributo=null;
			for(int f=0;f<atributos.length;f++){
				atributo = atributos[f];	
				atributo.setAccessible(true);
				Annotation annotationAtributo = atributo.getAnnotation(OrmAtributo.class);
				OrmAtributo orm = (OrmAtributo)annotationAtributo;
				if(ObjectUtil.isEmpty(orm))
					continue;
				if(ObjectUtil.isEmpty(atributo.get(objeto)) && !orm.chave())
					continue;
				Annotation annotationRef = atributo.getAnnotation(OrmReference.class);
				OrmReference ormRef = (OrmReference)annotationRef;
				if(!ObjectUtil.isEmpty(ormRef))
					continue;
				
				
				sql2.append(orm.nome_atribudo());
				if(f+1 != atributos.length)
					sql2.append(",");
				if(orm.chave()){
					if(ObjectUtil.isEmpty(ormNucleo.nome_sequencia())){
						recuperaValorAtributo(sql3,atributo,orm.tipo_atributo(),objeto);
					}else{	
						sql3.append("(SELECT nextval('");
						sql3.append(ormNucleo.nome_schema());
						sql3.append(".");
						sql3.append(ormNucleo.nome_sequencia());
						sql3.append("'))");
					}
				}else{		
					recuperaValorAtributo(sql3,atributo,orm.tipo_atributo(),objeto);
				}	
				if(f+1 != atributos.length)
					sql3.append(",");
			}
			removeUltimoCaracter(sql2,",");
			removeUltimoCaracter(sql3,",");
			sql2.append(")");
			sql3.append(")");
			connection = getConnection();
			statement = connection.createStatement();
			String sql = sql1.toString()+sql2.toString()+sql3.toString();
			int ret = statement.executeUpdate(sql);
			if(ret == 1)
				retorno = true;
			connection.commit();
		} catch (Exception e) {
			fechaConexao();
			e.printStackTrace();
		}finally{
			fechaConexao();
		}
		
		return retorno;
	}
	
	private void removeUltimoCaracter(StringBuffer sql2,String caracter) {
		if(sql2.toString().substring(sql2.toString().length()-1,sql2.toString().length()).equals(caracter))
			sql2.delete(sql2.toString().length()-1,sql2.toString().length());
	}
	
	protected boolean update(Object objeto){
		StringBuffer sql1 = new StringBuffer(" UPDATE ");
		StringBuffer sql2 = new StringBuffer(" SET ");
		StringBuffer sql3 = new StringBuffer(" WHERE ");
		boolean retorno= false;
		try {
			String nomeColuna= null;
			Field[] atributos = objeto.getClass().getDeclaredFields();
			Annotation annotation = objeto.getClass().getAnnotation(OrmNucleo.class);
			OrmNucleo ormNucleo = (OrmNucleo)annotation;
			sql1.append(ormNucleo.nome_schema());
			sql1.append(".");
			sql1.append(ormNucleo.nome_nucleo());
			Field atributo=null;
			for(int f=0;f<atributos.length;f++){
				atributo = atributos[f];	
				atributo.setAccessible(true);
				Annotation annotationAtributo = atributo.getAnnotation(OrmAtributo.class);
				OrmAtributo orm = (OrmAtributo)annotationAtributo;
				if(ObjectUtil.isEmpty(orm))
					continue;
				if(ObjectUtil.isEmpty(atributo.get(objeto)) && !orm.chave())
					continue;
				
				Annotation annotationRef = atributo.getAnnotation(OrmReference.class);
				OrmReference ormRef = (OrmReference)annotationRef;
				if(!ObjectUtil.isEmpty(ormRef))
					continue;
				
				sql2.append(orm.nome_atribudo());
				sql2.append(" = ");
				recuperaValorAtributo(sql2,atributo,orm.tipo_atributo(),objeto);
				if(f+1 != atributos.length)
					sql2.append(",");
				if(orm.chave()){
					sql3.append(orm.nome_atribudo());
					sql3.append(" = ");
					recuperaValorAtributo(sql3,atributo,orm.tipo_atributo(),objeto);
				}	
			}
			removeUltimoCaracter(sql2, ",");
			
			connection = getConnection();
			statement = connection.createStatement();
			String sql = sql1.toString()+sql2.toString()+sql3.toString();
			int ret = statement.executeUpdate(sql);
			if(ret == 1)
				retorno = true;
			connection.commit();
		} catch (Exception e) {
			fechaConexao();
			e.printStackTrace();
		}finally{
			fechaConexao();
		}
		
		return retorno;
	}
	
	protected boolean delete(Object objeto){
		StringBuffer sql1 = new StringBuffer(" DELETE FROM ");
		StringBuffer sql2 = new StringBuffer(" WHERE ");
		boolean retorno= false;
		try {
			String nomeColuna= null;
			Field[] atributos = objeto.getClass().getDeclaredFields();
			Annotation annotation = objeto.getClass().getAnnotation(OrmNucleo.class);
			OrmNucleo ormNucleo = (OrmNucleo)annotation;
			sql1.append(ormNucleo.nome_schema());
			sql1.append(".");
			sql1.append(ormNucleo.nome_nucleo());
			Field atributo=null;
			for(int f=0;f<atributos.length;f++){
				atributo = atributos[f];	
				atributo.setAccessible(true);
				Annotation annotationAtributo = atributo.getAnnotation(OrmAtributo.class);
				OrmAtributo orm = (OrmAtributo)annotationAtributo;
				if( ObjectUtil.isEmpty( orm ) )
					continue;
				
				Annotation annotationRef = atributo.getAnnotation(OrmReference.class);
				OrmReference ormRef = (OrmReference)annotationRef;
				if(!ObjectUtil.isEmpty(ormRef))
					continue;
				
				 if(sql2.length() > 10)
							sql2.append(" AND ");
				sql2.append(orm.nome_atribudo());
				sql2.append(" = ");
				recuperaValorAtributo(sql2,atributo,orm.tipo_atributo(),objeto);
			
			}
			connection = getConnection();
			statement = connection.createStatement();
			String sql = sql1.toString()+sql2.toString();
			int ret = statement.executeUpdate(sql);
			if(ret == 1)
				retorno = true;
			connection.commit();
		} catch (Exception e) {
			fechaConexao();
			e.printStackTrace();
		}finally{
			fechaConexao();
		}
		
		return retorno;
	}
	
	private void recuperaValorAtributo(StringBuffer sql3, Field atributo,String tipoAtributo,Object intancia) {
		atributo.setAccessible(true);
		try{
			if(tipoAtributo.equalsIgnoreCase(TipoAtributo.ALFANUMERICO)){
				sql3.append("'");
				
				if(atributo.getType().getName().equals("char"))
					sql3.append(((Character)atributo.get(intancia)));
				if(atributo.getType().getName().equals("java.lang.String"))
					sql3.append((String)atributo.get(intancia));
				
				sql3.append("'");
			}else if(tipoAtributo.equalsIgnoreCase(TipoAtributo.NUMERICO)){
				Object vl = atributo.get(intancia);
				if(vl instanceof Long)
					sql3.append((Long)vl);
				if(vl instanceof Integer)
					sql3.append((Integer)vl);
				if(vl instanceof Double)
					sql3.append((Double)vl);
			}else if(tipoAtributo.equalsIgnoreCase(TipoAtributo.DATA)){
				sql3.append("to_date('");
				sql3.append(sdf.format(((Date)atributo.get(intancia))));
				sql3.append("','DD/MM/YYYY HH24:MI:SS')");
			}else if(tipoAtributo.equalsIgnoreCase(TipoAtributo.TIMESTAMP)){
				sql3.append("to_timestamp('");
				sql3.append(sdf.format(((Date)atributo.get(intancia))));
				sql3.append("','DD/MM/YYYY HH24:MI:SS')");
			}
			else if(tipoAtributo.equalsIgnoreCase(TipoAtributo.DECIMAL)){
				Object vl = atributo.get(intancia);
				if(vl instanceof Double)
					sql3.append((Double)atributo.get(intancia));
				if(vl instanceof BigDecimal)
					sql3.append((BigDecimal)atributo.get(intancia));
			}
			else{
				System.out.println(1);
			}
		}catch(Exception e){
			e.printStackTrace();
		}
	}
	
	/**
	 * recupera uma conexao do pool de conexao
	 * @return
	 */
	protected final Connection getConnection(){
		try{	
			Connection connection = ConnectionProvider.getDefaultConnection();
//			Context ctx = new InitialContext(); 
//			DataSource ds = (DataSource) ctx.lookup("java:/sipmc-ds"); 
//			Connection connection = ds.getConnection();			
			return connection;
		}catch(Exception e){
			e.printStackTrace();
			return null;
		}
	}
	
	protected String montaParametro(String sql,String codigoParametro,String valorParametro){
		StringBuffer sb = new StringBuffer(sql);
		String vl = valorParametro.replace("'","");
		int local = sb.indexOf(codigoParametro);
		sb.delete(local,local+codigoParametro.length());
		sb.insert(local, vl);
		return sb.toString();
	}
	

	
	/**
	 * fecha as conexao e demais recurso obtido
	 * @param objeto
	 */
	protected final static void fechaConexao(Object objeto){
		try{
		if(objeto instanceof ResultSet)
			((ResultSet)objeto).close();
		else if(objeto instanceof Statement)
			((Statement)objeto).close();
		else if(objeto instanceof Connection)
			((Connection)objeto).close();
		else if(objeto instanceof CallableStatement)
			((CallableStatement)objeto).close();
		else if(objeto instanceof PreparedStatement)
			((PreparedStatement)objeto).close();
		}
		catch(Exception e){}
	}

	private void fechaConexao() {
		fechaConexao(connection);
		fechaConexao(resultSet);
		fechaConexao(statement);
		fechaConexao(metadado);
	}
	
}
